#文件名: dbmodel.py
import sys
sys.path.append('..')
from config import dbcfg
from functools import reduce
import pymysql

class db(object):
    config = dbcfg.dbconfig
    __db = None
    cursor = None
    def __init__(self):
        self.__db = pymysql.connect(self.config['localhost'],self.config['user'],self.config['passwd'],self.config['database'])
        self.cursor = self.__db.cursor()
        return None

    def select(self,sql):
        try:
            if(not isinstance(sql,str)):
                raise TypeError
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return data
        except TypeError:
            print('the param must be string')

    def find(self,sql):
        try:
            if(not isinstance(sql,str)):
                raise TypeError
            self.cursor.execute(sql)
            data = self.cursor.fetchone()
            return data
        except TypeError:
            print('the param must be string')

    def insert(self,sql,table = None):
        try:
            if (isinstance(sql, dict) or isinstance(sql, list)) and table == None:
                raise ValueError('the param of three cannot empty,if the sql of param is type of dict or list')
            if isinstance(sql, str):
                self.cursor.execute(sql)
                self.__db.commit()
                return self.cursor.lastrowid
            elif isinstance(sql, dict):
                sqlstr = "insert into {0}(" .format(table)
                culum = ''
                vale = ''
                for key,value in sql.items():
                    culum = culum + "%s,"  % key
                    if isinstance(value,str):
                        value = '"' + value + '"'
                    vale = vale + "%s,"  % str(value)
                culum = culum[:-1]
                vale = vale[:-1]
                sqlstr = sqlstr + culum + ') values(' + vale + ')'
                self.cursor.execute(sqlstr)
                self.__db.commit()
                return self.cursor.lastrowid
            else:
                raise TypeError('the sql of param must be str or dict')
        except ValueError:
            self.__db.rollback()
            print('the param of three cannot empty,if the sql of param is type of dict or list')
        else:
            print('insert failed,mysql error')
            return 0

    def update(self,sql:dict,condi:list,table = None):
        try:
            typeerr = ''
            if table == None:
                typeerr = 'the param table is empty'
                raise ValueError
            if not isinstance(sql,dict):
                typeerr = 'the param sql must be dict type'
                raise TypeError
            elif not isinstance(condi,list):
                typeerr = 'the param condi must be list type'
                raise TypeError
            else:
                sqlstr = 'update {0} set ' . format(table)
                for key,value in sql.items():
                    if isinstance(value,str):
                        value = '"' + value + '"'
                    sqlstr = sqlstr + str(key) + '=' + str(value) + ','
                sqlstr = sqlstr.rstrip(',') + " where 1=1 "
                for item in condi:
                    itemlen = len(item)
                    #判断condi里的值是否是tuple类型,且长度要么等于2要么等于3
                    if not isinstance(item,tuple) or itemlen not in [2,3]:
                        typeerr = 'the value of condi must be tuple and length or value must be between 2 or 3'
                        raise ValueError
                    if len(item) is 2:
                        itemval = item[1]
                        if isinstance(item[1], str):
                            itemval = '"' + item[1] + '"'
                        sqlstr = sqlstr + " and {0}={1}" .format(item[0],itemval)
                    else:
                        itemval = item[2]
                        if isinstance(item[2], str):
                            itemval = '"' + item[2] + '"'
                        sqlstr = sqlstr + " and {0} {1} {2}".format(item[0],item[1],itemval)
                upres = self.cursor.execute(sqlstr)
                self.__db.commit()
                return upres
        except ValueError:
            self.__db.rollback()
            print(typeerr)
            return -1
        else:
            self.__db.rollback()
            print('update failed,mysql error')
            return -1

    def delete(self,condi:list,table = None):
        try:
            typeerr = ''
            if table is None:
                typeerr = 'the param table is empty'
                raise ValueError
            elif not isinstance(condi,list):
                typeerr = 'the param condi must be list type'
                raise TypeError
            else:
                sqlstr = 'delete from {0} where 1=1 '.format(table)
                for item in condi:
                    itemlen = len(item)
                    #判断condi里的值是否是tuple类型,且长度要么等于2要么等于3
                    if not isinstance(item,tuple) or itemlen not in [2,3]:
                        typeerr = 'the value of condi must be tuple and length or value must be between 2 or 3'
                        raise ValueError
                    if len(item) is 2:
                        itemval = item[1]
                        if isinstance(item[1], str):
                            itemval = '"' + item[1] + '"'
                        sqlstr = sqlstr + " and {0}={1}" .format(item[0],itemval)
                    else:
                        itemval = item[2]
                        if isinstance(item[2], str):
                            itemval = '"' + item[2] + '"'
                        sqlstr = sqlstr + " and {0} {1} {2}".format(item[0],item[1],itemval)
                upres = self.cursor.execute(sqlstr)
                self.__db.commit()
                return upres
        except ValueError:
            self.__db.rollback()
            print(typeerr)
            return -1
        else:
            self.__db.rollback()
            print('update failed,mysql error')
            return -1

    def excute(self,sql):
        try:
            if(not isinstance(sql,str)):
                raise TypeError
            self.cursor.execute(sql)
            data = self.cursor.lastrowid
            return data
        except TypeError:
            print('the param must be string')


    def __del__(self):
        self.__db.close()

# dbcls = db()
# sql = {'name':'写代码','head_icon':'45643464dfgd6'}
# condition  = [('user_id','>',1),('account','like','%7777%')]
# res = dbcls.delete(condition,'uav_user')
# print(res)
